use proc_macro::TokenStream;
use quote::quote;
use serde::{Deserialize, Serialize};
use std::fmt::Debug;

use syn::{Data, DeriveInput, Fields, Ident};

#[derive(Serialize, Deserialize, Clone, Debug)]
struct Page<T: Sync + Send> {
    pub items: Vec<T>,
    pub total_count: i64,
    pub page_count: i64,
}


trait SnakeCase {
    fn to_snake_case(&self) -> String;
}

impl SnakeCase for String {
    fn to_snake_case(&self) -> String {
        let mut result = String::new();
        for (i, c) in self.chars().enumerate() {
            if c.is_uppercase() {
                if i != 0 {
                    result.push('_');
                }
                result.push(c.to_lowercase().next().unwrap());
            } else {
                result.push(c);
            }
        }
        result
    }
}

pub fn crud_derive(input: TokenStream) -> TokenStream {
    let input: DeriveInput = syn::parse(input).unwrap();
    let name = input.ident;
    let table_name = name.to_string().to_snake_case();

    let fields = match input.data {
        Data::Struct(data) => match data.fields {
            Fields::Named(fields) => fields.named,
            _ => panic!("SqlInsert can only be derived for structs with named fields"),
        },
        _ => panic!("SqlInsert can only be derived for structs"),
    };

    let field_names: Vec<Ident> = fields.iter().filter_map(|f| f.ident.clone()).collect();


    let add_query = quote! {
        pub async fn add(self, pool: &sqlx::Pool<Sqlite>) -> anyhow::Result<sqlx::sqlite::SqliteQueryResult> {
        let mut columns = Vec::new();
        let mut query = sqlx::query_builder::QueryBuilder::<sqlx::Sqlite>::new(format!("INSERT INTO {} ", #table_name));

        #(
            if let Some(value) = self.#field_names {
                columns.push(stringify!(#field_names),);
            }
        )*

        query.push("VALUES()");

        #(
             if let Some(ref value) = self.#field_names {
                   query.push_bind(value); // 绑定有效值
             }
        )*

        // 执行查询
        let result = query.build().execute(pool).await?;
        Ok(result)
    }

    };


    let insert_query = quote! {

            pub async fn insert(self, pool: &sqlx::Pool<Sqlite>) -> anyhow::Result<sqlx::sqlite::SqliteQueryResult> {
                let mut sql = format!("INSERT INTO {}(", #table_name);
                let mut sql_values = String::new();
                #(
                    if self.#field_names.is_some() {
                        sql.push_str(stringify!(#field_names));
                        sql.push_str(",");
                        sql_values.push_str("?,")
                    }
                )*
                sql.pop();
                sql_values.pop();
                sql.push_str(&format!(")VALUES({})",sql_values));
                let query = sqlx::query(&sql);
                #(
                    let query = query.bind(self.#field_names);
                )*
                let result = query.execute(pool).await?;
                Ok(result)
            }
    };

    let update_query = quote! {
        pub async fn update(self, pool: &sqlx::Pool<Sqlite>, id: i32) -> anyhow::Result<sqlx::sqlite::SqliteQueryResult> {
            let mut sql = format!("UPDATE {} SET ", #table_name);
            let mut set_clauses = String::new();

            #(
                if self.#field_names.is_some() {
                    set_clauses.push_str(&format!("{} = ?, ", stringify!(#field_names)));
                }
            )*

            sql.truncate(sql.len() - 2);
            sql.push_str(&format!(" WHERE {}_id = ?",#table_name));
            let mut query = sqlx::query(&sql);
            query = query.bind(id);
            let result = query.execute(pool).await?;
            Ok(result)
        }
    };

    let delete_query = quote! {
        pub async fn delete(&self,pool: &sqlx::Pool<Sqlite>, id: Option<i64>) -> anyhow::Result<sqlx::sqlite::SqliteQueryResult> {
            let sql = format!("DELETE FROM {} WHERE id = ?", #table_name);
            let result =  sqlx::query(&sql).bind(id).execute(pool).await?;
            Ok(result)
        }
    };

    let select_query = quote! {
        pub async fn select(&self,pool: &sqlx::Pool<Sqlite>, id: i32) -> anyhow::Result<Option<Self>> {
            let sql = format!("SELECT * FROM {} WHERE id = ?", #table_name);
            let record = sqlx::query_as::<_, Self>(&sql).bind(id).fetch_optional(pool).await?;
            Ok(record)
        }
    };

    let page_query = quote! {
        pub async fn page<T: Serialize + Debug + Send + Sync + Unpin + for<'r> sqlx::FromRow<'r, sqlx::sqlite::SqliteRow>>(self,pool:&sqlx::Pool<Sqlite>,paigation_options:(Option<i64>, Option<i64>)) -> anyhow::Result<Page<T>> {
            let mut sql = format!("SELECT * FROM {} WHERE 1=1 ", #table_name);

            #(
                if self.#field_names.is_some() {
                    sql.push_str(&format!(" and {} = ?, ", stringify!(#field_names)));
                }
            )*

             // 获取总记录数
             let count_query = format!("SELECT COUNT(*) FROM ({}) AS count_query", &sql);
             // 执行查询并获取结果
             let total_count: i64 = sqlx::query_scalar(&count_query)
                 .fetch_one(pool)
                 .await?;

            let offset = (paigation_options.0.unwrap_or(1) - 1) * paigation_options.1.unwrap_or(10);

            // 构建带有 LIMIT 和 OFFSET 的分页查询语句
            let paginated_query = format!("{} LIMIT {} OFFSET {}", sql, paigation_options.1.unwrap_or(10), offset);
            // 执行查询
            println!("分页sql = {}", &paginated_query);


            let query = sqlx::query_as(&paginated_query);
            #(
                let query = query.bind(self.#field_names);
            )*
            let items:Vec<T>  = query.fetch_all(pool).await?;

            // 构建 Page 结构体
            let page = Page {
                items,
                total_count,
                page_count: (total_count + paigation_options.1.unwrap_or(10) - 1) / paigation_options.1.unwrap_or(10),
            };
            Ok(page)
        }
    };

    let crud = quote! {
        impl #name {
           #add_query
            #insert_query
            #update_query
            #delete_query
            #select_query
            #page_query
        }
    };

    TokenStream::from(crud)
}